-- HydroPulse-DX 数据库初始化脚本
-- 自动创建TimescaleDB扩展和基础表结构

-- 创建TimescaleDB扩展
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- 创建时序表 (TimescaleDB优化)
CREATE TABLE IF NOT EXISTS hydrometric_data_timeseries (
    id SERIAL,
    station_id VARCHAR(50) NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    flow_rate FLOAT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (station_id, timestamp)
);

-- 转换为TimescaleDB hypertable - 性能优化配置
-- 将chunk_time_interval从1天调整为7天，减少chunk数量，提升查询性能
SELECT create_hypertable('hydrometric_data_timeseries', 'timestamp', 
                        partitioning_column => 'station_id',
                        number_partitions => 4,
                        chunk_time_interval => INTERVAL '7 days',
                        if_not_exists => TRUE);

-- 启用压缩功能
ALTER TABLE hydrometric_data_timeseries SET (timescaledb.compress = true);

-- 调整压缩策略从7天延长到30天，减少频繁查询数据的解压开销
SELECT add_compression_policy('hydrometric_data_timeseries', INTERVAL '30 days');

-- 可即时压缩老 chunk（测试环境先跑）
-- SELECT compress_chunk('_hyper_12_3353_chunk'::regclass);

-- 创建普通表 (PostgreSQL + 索引)
CREATE TABLE IF NOT EXISTS hydrometric_data_regular (
    id SERIAL PRIMARY KEY,
    station_id VARCHAR(50) NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    flow_rate FLOAT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 创建复合索引
CREATE INDEX IF NOT EXISTS idx_regular_station_timestamp 
ON hydrometric_data_regular (station_id, timestamp);

CREATE INDEX IF NOT EXISTS idx_regular_timestamp 
ON hydrometric_data_regular (timestamp);

CREATE INDEX IF NOT EXISTS idx_regular_station_id 
ON hydrometric_data_regular (station_id);

-- 手动运维函数
CREATE OR REPLACE FUNCTION compress_old_chunks(
    table_name TEXT,
    older_than INTERVAL,
    batch_size INT DEFAULT 10,
    sleep_interval INT DEFAULT 2
)
RETURNS VOID AS $$
DECLARE
    chunks TEXT[];
    total INT;
    i INT := 1;
BEGIN
    -- 获取所有符合条件的 chunk
    SELECT array_agg(chunk_name) INTO chunks
    FROM (
        SELECT show_chunks(table_name, older_than => older_than) AS chunk_name
    ) s;

    IF chunks IS NULL OR array_length(chunks, 1) = 0 THEN
        RAISE NOTICE 'No chunks older than % found for table % to compress.', older_than, table_name;
        RETURN;
    END IF;

    total := array_length(chunks, 1);
    RAISE NOTICE 'Total chunks to compress for table %: %', table_name, total;

    -- 批量压缩
    WHILE i <= total LOOP
        PERFORM compress_chunk(c::regclass)
        FROM unnest(chunks[i:LEAST(i + batch_size - 1, total)]) AS c;

        RAISE NOTICE 'Compressed chunks % to %', i, LEAST(i + batch_size - 1, total);

        -- 避免IO过高，可以调节
        PERFORM pg_sleep(sleep_interval);

        i := i + batch_size;
    END LOOP;

END;
$$ LANGUAGE plpgsql;

-- 输出初始化完成信息
\echo 'HydroPulse-DX 数据库初始化完成！'
\echo '- TimescaleDB扩展已启用'
\echo '- 时序表和普通表已创建'
\echo '- 索引和压缩策略已配置'
\echo '准备开始性能对比测试...'